﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using MarginTrading.Common;
using System.Data.OracleClient;
using System.Data;
using System.Windows.Forms;

namespace MarginTrading.Services
{
    public class AdvisorServices
    {
        private static Advisor GetAdvisorFromDataReader(IDataReader dataReader)
        {
            Advisor retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Advisor();
                retVal.Code = dataReader["CODE"].ToString();
                retVal.Title = dataReader["TITLE"].ToString();
                retVal.Initials = dataReader["INITIALS"].ToString();
                retVal.Name = dataReader["NAME"].ToString();
                retVal.Surname = dataReader["SURNAME"].ToString();
                retVal.Address_1 = dataReader["ADDRESS_1"].ToString();
                retVal.Address_2 = dataReader["ADDRESS_2"].ToString();
                retVal.Address_3 = dataReader["ADDRESS_3"].ToString();
                retVal.Address_4 = dataReader["ADDRESS_4"].ToString();
                retVal.Telephone1 = dataReader["TELEPHONE_1"].ToString();
                retVal.Telephone2 = dataReader["TELEPHONE_2"].ToString();
                retVal.Fax = dataReader["FAX"].ToString();
                DateTime createdDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["CREATED_DATE"].ToString(), out createdDate))
                    retVal.CreatedDate = createdDate;
            }
            return retVal;
        }

        public static Advisor GetAdvisorByCode(string code)
        {
            Advisor retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from advisor where code = '" + code + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetAdvisorFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static AdvisorCollection GetAdvisorCollection()
        {
            AdvisorCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from advisor";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new AdvisorCollection();
                    while (dataReader.Read())
                    {
                        Advisor item = GetAdvisorFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static AutoCompleteStringCollection GetAdvisorACCollection()
        {
            AutoCompleteStringCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CODE from advisor";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new AutoCompleteStringCollection();
                    while (dataReader.Read())
                    {
                        string item = dataReader["CODE"].ToString();
                        if (!string.IsNullOrEmpty(item))
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetAdvisorCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from advisor order by code";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static void Add(Advisor advisor)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "insert into advisor(CODE,NAME,TITLE,INITIALS,SURNAME,ADDRESS_1,ADDRESS_2,ADDRESS_3," +
                                  "ADDRESS_4,TELEPHONE_1,TELEPHONE_2,FAX,CREATED_DATE)" +
                                  "values(:v1,:v2,:v3,:v4,:v5,:v6,:v7,:v8,:v9,:v10,:v11,:v12,:v13)";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = advisor.Code;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = advisor.Name;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = advisor.Title;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = advisor.Initials;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = advisor.Surname;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = advisor.Address_1;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = advisor.Address_2;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = advisor.Address_3;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = advisor.Address_4;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = advisor.Telephone1;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = advisor.Telephone1;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = advisor.Fax;
                cmd.Parameters.Add(":v13", OracleType.DateTime).Value = advisor.CreatedDate;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string code)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from advisor where code = '" + code + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Advisor advisor)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update advisor set " +
                                  "NAME = :v2, " +
                                  "TITLE = :v3, " +
                                  "INITIALS = :v4, " +
                                  "SURNAME = :v5, " +
                                  "ADDRESS_1 = :v6, " +
                                  "ADDRESS_2 = :v7, " +
                                  "ADDRESS_3 = :v8, " +
                                  "ADDRESS_4 = :v9, " +
                                  "TELEPHONE_1 = :v10, " +
                                  "TELEPHONE_2 = :v11, " +
                                  "FAX = :v12, " +
                                  "CREATED_DATE = :v13 where CODE = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = advisor.Code;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = advisor.Name;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = advisor.Title;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = advisor.Initials;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = advisor.Surname;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = advisor.Address_1;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = advisor.Address_2;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = advisor.Address_3;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = advisor.Address_4;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = advisor.Telephone1;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = advisor.Telephone1;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = advisor.Fax;
                cmd.Parameters.Add(":v13", OracleType.DateTime).Value = advisor.CreatedDate;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
